import datetime

from other.cfg import Config
from other.change_format import change_format
from other.sql_montage import sqlMontage
from other.status import Status
from server.Mysql import MySQL


class user:
    def __init__(self):
        self.cfg = Config()
        self.st = Status()
        self.db = MySQL(self.cfg.res['mysql']['database'], 'sys_user')
        self.cf = change_format()
        self.sm = sqlMontage()

    # 获取登录用户信息
    def get_user_info(self, user_id):
        result = {'msg': '成功', 'code': 200, 'permissions': [], 'roles': [], 'user': {}}
        sql = f"where `user_id`={user_id} and `del_flag`='{self.st.get_status('delFlag', '存在')}'"
        # print(sql)
        u_res = self.db.get(sql)
        if u_res != ():
            for u in u_res:
                u.pop('password', None)
                for key, value in u.items():
                    if isinstance(value, datetime.datetime):
                        u[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                # d_sql = "select sd.`dept_id`,sd.`dept_name`,REVERSE(GROUP_CONCAT(DISTINCT p.`dept_name` SEPARATOR '/')) as ancestors_name from `sys_user` as su left join `sys_dept` as sd on sd.`dept_id`=su.`dept_id` and sd.`del_flag`='{}' LEFT JOIN `sys_dept` as p on FIND_IN_SET(p.`dept_id`, sd.`ancestors`) where su.`user_id`={} and su.`del_flag`='{}' GROUP BY su.`user_id`".format(
                #     self.st.get_status('del_flag', '存在'), u['user_id'],
                #     self.st.get_status('del_flag', '存在'))
                # d_res = db.gets(d_sql)
                # u.update(d_res[0])
                u['dept'] = {}
                u['roles'] = []
                r_sql = "select sr.* from `sys_user` as su join `sys_user_role` as sur on su.`user_id`=sur.`user_id` join `sys_role` as sr on sr.`role_id`=sur.`role_id` and sr.`del_flag`='{}' where su.`user_id`={} and su.`del_flag`='{}'".format(
                    self.st.get_status('delFlag', '存在'), u['user_id'], self.st.get_status('delFlag', '存在'))
                r_res = self.db.gets(r_sql)
                # print('r_res', r_res)
                if r_res != ():
                    for r in r_res:
                        for key, value in r.items():
                            if isinstance(value, datetime.datetime):
                                r[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                        if r['role_key'] == 'admin':
                            result['permissions'].append('*:*:*')
                        result['roles'].append(r['role_key'])
                        u['roles'].append(r)
                        # 获取菜单权限
                        rm_sql = "select sm.`perms` from `sys_menu` sm join `sys_role_menu` srm on sm.`menu_id`=srm.`menu_id` where srm.`role_id`={}".format(
                            r['role_id'])
                        # print('rm_sql', rm_sql)
                        rm_res = self.db.gets(rm_sql)
                        if rm_res != ():
                            for rm in rm_res:
                                result['permissions'].append(rm['perms'])
                result['user'] = u
        result = self.cf.changes_format(result)
        return result

    # 获取用户信息
    def get_users(self, data):
        result = {'msg': '成功', 'code': 200, 'data': [], 'total': None}
        sqls = "where `del_flag`='{}' ".format(self.st.get_status('delFlag', '存在'))
        sql = ""
        pages = ''
        if data != {}:
            if data.get('pageNum') is not None and data.get('pageSize') is not None:
                if data['pageNum'] != '' and data['pageSize'] != '':
                    pages = " limit {},{}".format(int(data['pageNum']) * int(data['pageSize']) - int(data['pageSize']),
                                                 int(data['pageSize']))
            if data.get('userName') is not None:
                if data['userName'] != '':
                    sql = (sql + " and `user_name` like '%{}%'".format(data['userName']))
            if data.get('status') is not None:
                if data['status'] != '':
                    sql = sql + " and `status`='{}'".format(data['status'])
            if data.get('phoneNumber') is not None:
                if data['phoneNumber'] != '':
                    sql = sql + " and `phone_number`='{}'".format(data['phoneNumber'])
            if data.get('beginTime') is not None and data.get('endTime') is not None:
                if data['beginTime'] != '' and data['endTime'] != '':
                    sql = (sql + " and ('{}'<=`create_time` and `create_time`<='{}')".
                           format(data['beginTime'], data['endTime']))
        # print('total', sqls + sql)
        total = self.db.get(sqls + sql)
        result['total'] = len(total)
        if sql != "":
            sql = sqls + sql + f'{pages}'
        else:
            sql = sqls + f'{pages}'
        # print(sql)
        res = self.db.get(sql)
        if res != ():
            for r in res:
                r.pop('password', None)
                # d['user_list'] = []
                for key, value in r.items():
                    if isinstance(value, datetime.datetime):
                        r[key] = value.strftime("%Y-%m-%d %H:%M:%S")
                result['data'].append(r)
        result = self.cf.changes_format(result)
        return result

    # 获取单个用户信息
    def get_userInfo(self, user_id):
        result = {'msg': '成功', 'code': 200, 'data': {}, 'roleIds': []}
        res = self.get_user_info(user_id)
        if res['user'] != {}:
            for rr in res['user']['roles']:
                result['roleIds'].append(rr['roleId'])
        result['data'] = res['user']
        result = self.cf.changes_format(result)
        return result

    # 新增用户
    def user_add(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        g_sql = f"where (`user_name`='{data['userName']}' or `phone_number`='{data['phoneNumber']}') and `del_flag`='{self.st.get_status('delFlag', '存在')}'"
        g_res = self.db.get(g_sql)
        if g_res != ():
            result['code'] = 500
            for g in g_res:
                if g['user_name'] == data['userName']:
                    result['msg'] = '用户名重复'
                if g['phone_number'] == data['phoneNumber']:
                    result['msg'] = '电话重复'
        else:
            # add_sql = f"(`user_name`,`nick_name`,`email`,`phone_number`,`sex`,`password`,`status`,`create_by`,`create_time`,`remark`) values ('{data['userName']}','{data['nickName']}','{data['email']}','{data['phoneNumber']}','{data['sex']}','{data['password']}','{data['status']}','{userInfo['userName']}','{times}','{data['remark']}')"
            # add_sql = add_sql.replace("'None'", "NULL").replace("None", "NULL")
            # user_id = self.db.add(add_sql)
            data['create_by'] = userInfo['userName']
            data['create_time'] = times
            add_sql = self.sm.add_sql_data(data, 'sys_user')
            # print('add_sql', add_sql)
            user_id = self.db.adds(add_sql, 'sys_user')
            if data['roleIds']:
                for rr in data['roleIds']:
                    dd = {'user_id': user_id, 'role_id': rr}
                    add_role_sql = self.sm.add_sql_data(dd, 'sys_user_role')
                    # print('add_role_sql', add_role_sql)
                    self.db.adds(add_role_sql, 'sys_user_role')

                    # add_role_sql = f"insert into `sys_user_role` (`user_id`,`role_id`) values  ({user_id},{rr})"
                    # self.db.edits(add_role_sql, 'sys_user_role')
        return result

    # 用户修改
    def user_edit(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        g_sql = f"where `phone_number`='{data['phoneNumber']}' and `del_flag`='{self.st.get_status('delFlag', '存在')}'"
        g_res = self.db.get(g_sql)
        ff = False
        if g_res != ():
            for g in g_res:
                if g['user_id'] == data['userId']:
                    ff = True
                else:
                    ff = False
                    result['code'] = 500
                    result['msg'] = '电话号码已存在'
                    break
        else:
            ff = True
        if ff:
            # e_sql = f"`nick_name`='{data['nickName']}',`email`='{data['email']}',`phone_number`='{data['phoneNumber']}',`sex`='{data['sex']}',`status`='{data['status']}',`update_by`='{userInfo['userName']}',`update_time`='{times}',`remark`='{data['remark']}' where `user_id`={data['userId']}"
            # self.db.edit(e_sql)

            if 'createTime' in data:
                del data['createTime']
            data['update_by'] = userInfo['userName']
            data['update_time'] = times
            edit_sql = self.sm.edit_sql_data(data, 'sys_user', 'user_id', data['userId'])
            # print('edit_sql', edit_sql)
            self.db.edits(edit_sql, 'sys_user')

            # 删除所有权限
            d_sql = f"delete from `sys_user_role` where `user_id`={data['userId']}"
            self.db.edits(d_sql, 'sys_user_role')
            if data['roleIds']:
                for rr in data['roleIds']:
                    dd = {'user_id': data['userId'], 'role_id': rr}
                    add_role_sql = self.sm.add_sql_data(dd, 'sys_user_role')
                    # print('add_role_sql', add_role_sql)
                    self.db.adds(add_role_sql, 'sys_user_role')

                    # add_role_sql = f"insert into `sys_user_role` (`user_id`,`role_id`) values  ({data['userId']},{rr})"
                    # self.db.edits(add_role_sql, 'sys_user_role')
        return result

    # 密码重置
    def user_resetPwd(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        if 'createTime' in data:
            del data['createTime']
        data['update_by'] = userInfo['userName']
        data['update_time'] = times
        edit_sql = self.sm.edit_sql_data(data, 'sys_user', 'user_id', data['userId'])
        # print('edit_sql', edit_sql)
        self.db.edits(edit_sql, 'sys_user')

        # e_sql = f"`password`='{data['password']}',`update_by`='{userInfo['userName']}',`update_time`='{times}' where `user_id`={data['userId']}"
        # self.db.edit(e_sql)
        return result

    # 用户删除
    def user_del(self, data, userInfo):
        result = {'msg': '成功', 'code': 200}
        times = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        ids = [int(x) for x in data.split(',')]
        for i in ids:
            e_sql = f"`del_flag`='{self.st.get_status('delFlag', '已删除')}',`update_by`='{userInfo['userName']}',`update_time`='{times}' where `user_id`={i}"
            self.db.edit(e_sql)
            # 删除角色
            dr_sql = f"delete from `sys_user_role` where `user_id`={i}"
            self.db.edits(dr_sql, 'sys_user_role')
        return result
